﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using EM.Repository.utility;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Web.Security;

namespace AlternateProject.Common
{
    public partial class Login : System.Web.UI.UserControl
    {
        DbHelper dsrv = new DbHelper();

        protected void Page_Load(object sender, EventArgs e)
        {
            txtUserName.Focus();

            if (!IsPostBack)
            {
                Session.Abandon();
            }
        }

        private bool passCount(string Pass)
        {
            bool value = true;
            string sSQL = "";

            sSQL += " SELECT U.USERID, U.USERCODE, U.USERPASS, U.USERDESC, U.USEREMAIL, ISNULL(U.WRONGPASSCOUNT,0)WRONGPASSCOUNT, ";
            sSQL += " ISNULL(U.STATUS,'I') AS STATUS, ISNULL(U.PASSIVEDESC,'') PASSIVEDESC,  ";
            sSQL += " CASE WHEN  ISNULL(U.MAXCONCURRENTCONNECTIONCOUNT,0)= 0 THEN  ";
            sSQL += " (	SELECT MAX  (R.MAXSESSIONCOUNT) FROM TBLUSERROLE UR , TBLROLE R  ";
            sSQL += " 	WHERE UR.USERID = U.USERID  ";
            sSQL += " 	AND R.ROLEID = UR.ROLEID ";
            sSQL += " ) ELSE  ISNULL(U.MAXCONCURRENTCONNECTIONCOUNT,0) END  AS  USERGROUP_MAXCONCURRENTCONNECTIONCOUNT, ";
            sSQL += " ISNULL(U.MAXCONCURRENTCONNECTIONCOUNT,0) USER_MAXCONCURRENTCONNECTIONCOUNT ";
            sSQL += " FROM TBLUSER U";
            sSQL += " WHERE 1=1 ";
            sSQL += " AND U.USERCODE = @USERCODE  ";
            sSQL += " AND GETDATE() BETWEEN DATEADD(D, -1, U.STARTDATE) AND ISNULL(U.ENDDATE,'01/01/2100')  ";


            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();
            cmd.CommandText = sSQL;
            cmd.Parameters.Add("USERCODE", SqlDbType.NVarChar).Value = txtUserName.Text;
            dt = dsrv.GetDataTable(cmd, "TBLUSER");

            if (dt.Rows.Count > 0)
            {
                int WRONGPASSCOUNT = Convert.ToInt32(dt.Rows[0]["WRONGPASSCOUNT"].ToString()) + 1;

                if (Pass != dt.Rows[0]["USERPASS"].ToString())
                {
                    value = true;

                    sSQL = "";
                    sSQL += " UPDATE TBLUSER SET";
                    sSQL += " WRONGPASSCOUNT = " + WRONGPASSCOUNT.ToString();
                    if (WRONGPASSCOUNT >= 3)
                    {
                        value = false;

                        sSQL += ", ";
                        sSQL += " STATUS ='I',";
                        sSQL += " PASSIVEDESC = 'şifreniz iptal edilmiştir. Sistem yetkilisi ile görüşünüz.'";
                    }
                    sSQL += " WHERE USERID = " + dt.Rows[0]["USERID"].ToString();

                    dsrv.ExecuteNonQuery(sSQL);
                }
                else
                {
                    if (WRONGPASSCOUNT <= 3)
                    {
                        value = true;

                        sSQL = "";
                        sSQL += " UPDATE TBLUSER SET";
                        sSQL += " WRONGPASSCOUNT = " + 0;
                        sSQL += " WHERE USERID = " + dt.Rows[0]["USERID"].ToString();

                        dsrv.ExecuteNonQuery(sSQL);
                    }
                    else
                    {
                        value = false;
                    }
                }
            }

            return value;
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string sURL = "";
            if (Request.QueryString["url"] != null)
            {
                sURL = Request.QueryString["url"].ToString();
            }
            else
            {
                sURL = (new AppPathFinder()).GetApplicationPath() + "/default.aspx";
            }

            if (passCount(FormsAuthentication.HashPasswordForStoringInConfigFile(txtPassword.Text, "md5")))
            {
                string sSQL = "";
                sSQL += "  SELECT U.USERID, U.USERCODE, U.USERDESC, U.USEREMAIL,";
                sSQL += "  ISNULL(U.STATUS,'I') AS STATUS, ISNULL(U.PASSIVEDESC,'') PASSIVEDESC, ";
                sSQL += "   CASE WHEN  ISNULL(U.MAXCONCURRENTCONNECTIONCOUNT,0)= 0 THEN   ";
                sSQL += "  (	ISNULL((SELECT MAX  (R.MAXSESSIONCOUNT) FROM TBLUSERROLE UR , TBLROLE R   ";
                sSQL += "  	            WHERE UR.USERID = U.USERID   ";
                sSQL += "  	            AND R.ROLEID = UR.ROLEID),0)  ";
                sSQL += "  ) ELSE  ISNULL(U.MAXCONCURRENTCONNECTIONCOUNT,0) END  AS USERGROUP_MAXCONCURRENTCONNECTIONCOUNT,  ";
                sSQL += "  ISNULL(U.MAXCONCURRENTCONNECTIONCOUNT,0) USER_MAXCONCURRENTCONNECTIONCOUNT";
                sSQL += "  FROM TBLUSER U";
                sSQL += "  WHERE 1 = 1 ";
                sSQL += "  AND U.USERCODE = @USERCODE  ";
                sSQL += " AND U.USERPASS = @USERPASS ";


                SqlCommand cmd = new SqlCommand();
                DataTable dtUserInfo = new DataTable();

                cmd.CommandText = sSQL;
                cmd.Parameters.Add("USERPASS", SqlDbType.NVarChar).Value = FormsAuthentication.HashPasswordForStoringInConfigFile(txtPassword.Text, "md5");
                cmd.Parameters.Add("USERCODE", SqlDbType.NVarChar).Value = txtUserName.Text;
                dtUserInfo = dsrv.GetDataTable(cmd, "TBLUSER");

                if (dtUserInfo.Rows.Count > 0)
                {
                    if (dtUserInfo.Rows[0]["STATUS"].ToString() != "I")
                    {
                        sSQL = " SELECT COUNT(*) OPENSESSIONCOUNT ";
                        sSQL += " FROM TBLACTIVESESSION";
                        sSQL += " WHERE USERID = " + dtUserInfo.Rows[0]["USERID"].ToString();

                        DataTable dtOpenSession = new DataTable();
                        dtOpenSession = dsrv.GetDataTable(sSQL, "TBLOPENSESSION");

                        //kullanıcı için open session limiti
                        int USER_MAXCONCURRENTCONNECTIONCOUNT = Convert.ToInt32(dtUserInfo.Rows[0]["USER_MAXCONCURRENTCONNECTIONCOUNT"].ToString());
                        //kullanıcı grubu bazında open session limiti 
                        int USERGROUP_MAXCONCURRENTCONNECTIONCOUNT = Convert.ToInt32(dtUserInfo.Rows[0]["USERGROUP_MAXCONCURRENTCONNECTIONCOUNT"].ToString());
                        //varsa kullanıcı open session limiti, yoksa grubu bazında open session limiti
                        int iMAXCONCURRENTCONNECTIONCOUNT = USER_MAXCONCURRENTCONNECTIONCOUNT > 0 ? USER_MAXCONCURRENTCONNECTIONCOUNT : USERGROUP_MAXCONCURRENTCONNECTIONCOUNT;

                        int OPENSESSIONCOUNT = Convert.ToInt32(dtOpenSession.Rows[0]["OPENSESSIONCOUNT"].ToString());

                        if (OPENSESSIONCOUNT >= iMAXCONCURRENTCONNECTIONCOUNT && OPENSESSIONCOUNT > 0)
                        {
                            //ilk açılmış oturumu otomatik olarak kapat ve öyle oturum aç
                            sSQL = "SELECT TOP 1 SESSIONHEADERID";
                            sSQL += " FROM TBLACTIVESESSION";
                            sSQL += " WHERE USERID = " + dtUserInfo.Rows[0]["USERID"].ToString();
                            sSQL += " ORDER BY LASTACCESSDATE ASC";

                            DataTable dtFirstOpenedSession = new DataTable();
                            dtFirstOpenedSession = dsrv.GetDataTable(sSQL, "TBLFIRSTOPENEDSESSION");

                            sSQL = "UPDATE TBLSESSIONHEADER";
                            sSQL += " SET ENDDATE = GETDATE()";
                            sSQL += " WHERE SESSIONHEADERID = '" + dtFirstOpenedSession.Rows[0]["SESSIONHEADERID"].ToString() + "';";

                            sSQL += " DELETE FROM TBLACTIVESESSION WHERE SESSIONHEADERID = " + dtFirstOpenedSession.Rows[0]["SESSIONHEADERID"].ToString() + ";";

                            dsrv.ExecuteNonQuery(sSQL);
                        }

                        OpenSession(dtUserInfo);
                        setUserPageFunctionHash();
                        //amLogin.ResponseScripts.Add(string.Format("window.location.href='{0}';", sURL));
                        Page.ClientScript.RegisterStartupScript(this.GetType(), "", string.Format("window.location.href='{0}';", sURL), true);
                        //Response.Redirect(sURL);
                    }
                    else
                    {
                        lblError.Text = "Hesabınız sistem yöneticisi tarafından iptal edilmiş. ıptal Sebebi:";
                        if (dtUserInfo.Rows[0]["PASSIVEDESC"].ToString() != "")
                        {
                            lblError.Text += dtUserInfo.Rows[0]["PASSIVEDESC"].ToString();
                        }
                        else { lblError.Text += " Sebep belirtilmemiş. "; }
                    }
                }
                else { lblError.Text = "Hatalı kullanıcı adı ya da şifre. "; }
            }
            else { lblError.Text = "Şifreniz iptal edilmiştir. Sistem yetkilisi ile görüşünüz."; }
        }

        private void setUserPageFunctionHash()
        {
            DataTable dtAuthorizedPages = new DataTable();
            Hashtable htAuthorizedFunctions = new Hashtable();
            Hashtable htTemp = new Hashtable();
            SqlDataReader drAuthorizedFunction;
            string sSQL = "";

            sSQL = " SELECT PF.PAGEID ";
            sSQL += " FROM TBLROLEPAGEFUNCTION RPF, TBLPAGEFUNCTION PF ";
            sSQL += " WHERE RPF.PAGEFUNCTIONID = PF.PAGEFUNCTIONID ";
            sSQL += " AND GETDATE() BETWEEN RPF.STARTDATE AND ISNULL(RPF.ENDDATE,'01/01/2099')  ";
            sSQL += " AND RPF.ROLEID IN ( ";
            sSQL += " 	SELECT UR.ROLEID  ";
            sSQL += " 	FROM TBLUSERROLE UR, TBLROLE R ";
            sSQL += " 	WHERE USERID = " + Session["USERID"].ToString();
            sSQL += " 	AND UR.ROLEID = R.ROLEID ";
            sSQL += " 	AND GETDATE() BETWEEN UR.STARTDATE AND ISNULL(UR.ENDDATE,'01/01/2099') ";
            sSQL += " 	AND GETDATE() BETWEEN R.STARTDATE AND ISNULL(R.ENDDATE,'01/01/2099') ";
            sSQL += " ) ";
            sSQL += " GROUP BY PF.PAGEID ";

            dtAuthorizedPages = dsrv.GetDataTable(sSQL, "");

            if (dtAuthorizedPages.Rows.Count > 0)
            {
                /*drAppLanguage içerisindeki tüm kayıtları ve ona bağlı Mesajları htMessages içine alalım*/

                for (int i = 0; i < dtAuthorizedPages.Rows.Count; i++)
                {
                    htTemp.Clear();

                    sSQL = " SELECT PF.FUNCTIONCODE ";
                    sSQL += " FROM TBLROLEPAGEFUNCTION RPF, TBLPAGEFUNCTION PF ";
                    sSQL += " WHERE RPF.PAGEFUNCTIONID = PF.PAGEFUNCTIONID ";
                    sSQL += " AND GETDATE() BETWEEN RPF.STARTDATE AND ISNULL(RPF.ENDDATE,'01/01/2099')  ";
                    sSQL += " AND RPF.ROLEID IN ( ";
                    sSQL += " 	SELECT UR.ROLEID  ";
                    sSQL += " 	FROM TBLUSERROLE UR, TBLROLE R ";
                    sSQL += " 	WHERE USERID = " + Session["USERID"].ToString();
                    sSQL += " 	AND UR.ROLEID = R.ROLEID ";
                    //sSQL += " 	AND R.SYS_STATUS = 'A' ";
                    sSQL += " 	AND GETDATE() BETWEEN UR.STARTDATE AND ISNULL(UR.ENDDATE,'01/01/2099') ";
                    sSQL += " 	AND GETDATE() BETWEEN R.STARTDATE AND ISNULL(R.ENDDATE,'01/01/2099') ";
                    sSQL += " ) ";
                    sSQL += " AND PF.PAGEID = " + dtAuthorizedPages.Rows[i]["PAGEID"].ToString();
                    sSQL += " GROUP BY PF.PAGEID, PF.FUNCTIONCODE ";

                    drAuthorizedFunction = dsrv.CreateSqlDataReader(sSQL);

                    while (drAuthorizedFunction.Read())
                    {
                        htTemp.Add(drAuthorizedFunction["FUNCTIONCODE"].ToString(), drAuthorizedFunction["FUNCTIONCODE"].ToString());
                    }

                    htAuthorizedFunctions.Add(dtAuthorizedPages.Rows[i]["PAGEID"].ToString(), htTemp.Clone());
                    drAuthorizedFunction.Close();
                }
            }

            HttpContext.Current.Session["USERAUTHORIZEDPAGEFUNCTION"] = htAuthorizedFunctions;
            drAuthorizedFunction = null;
            dtAuthorizedPages = null;
            dsrv = null;
            htAuthorizedFunctions = null;
            htTemp = null;

        }

        void OpenSession(DataTable dtSession)
        {
            //oturum aç
            Session["USERCODE"] = dtSession.Rows[0]["USERCODE"].ToString();
            Session["USERDESC"] = dtSession.Rows[0]["USERDESC"].ToString();
            Session["USERID"] = Convert.ToInt32(dtSession.Rows[0]["USERID"].ToString());
            Session["LANGCODE"] = "TR";
            Session["DECIMALSEPERATOR"] = ",";
            Session["DIGITSEPERATOR"] = ".";

            if (Session["LANGCODE"].ToString() == "EN")
            {
                HttpContext.Current.Session["CULTURECODE"] = "en-US";
            }
            else if (Session["LANGCODE"].ToString() == "TR")
            {
                HttpContext.Current.Session["CULTURECODE"] = "tr-TR";
            }

            string sSESSIONSERVERID = HttpContext.Current.Session.SessionID.ToString();
            string sSQL = "";

            sSQL = " DECLARE @STARTDATE DATETIME";
            sSQL += " DECLARE @SESSIONHEADERID INT;";
            sSQL += " DECLARE @ACTIVESESSIONID INT;";
            sSQL += " SET @STARTDATE = GETDATE()";

            sSQL += " INSERT INTO TBLSESSIONHEADER (SESSIONID, USERID, STARTDATE, ENDDATE, IPADDRESS)";
            sSQL += " SELECT @SESSIONSERVERID, @USERID, @STARTDATE, NULL, @IPADDRESS";

            sSQL += " SET @SESSIONHEADERID = SCOPE_IDENTITY();";

            sSQL += " INSERT INTO TBLACTIVESESSION(LASTACCESSDATE, USERID, SESSIONHEADERID)";
            sSQL += " SELECT @STARTDATE, @USERID, @SESSIONHEADERID";

            sSQL += " SET @ACTIVESESSIONID = SCOPE_IDENTITY();";
            sSQL += " SELECT @SESSIONHEADERID, @ACTIVESESSIONID;";

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sSQL;
            cmd.Parameters.Add("SESSIONSERVERID", SqlDbType.NVarChar).Value = sSESSIONSERVERID;
            cmd.Parameters.Add("USERID", SqlDbType.Int).Value = Session["USERID"];
            cmd.Parameters.Add("IPADDRESS", SqlDbType.NVarChar).Value = HttpContext.Current.Request.UserHostAddress;

            DataTable dt = new DataTable();

            dt = dsrv.GetDataTable(cmd, "TBLSESSIONHEADER");

            HttpContext.Current.Session["SESSIONHEADERID"] = Convert.ToInt32(dt.Rows[0][0].ToString());
            HttpContext.Current.Session["ACTIVESESSIONID"] = Convert.ToInt32(dt.Rows[0][1].ToString());

        }
    }
}